﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Maticsoft.DBUtility;
using System.Data.SqlClient;

namespace ElingOA6_Admin_DSJCJ.DAL
{
    /// <summary>
    /// 工作组DAL
    /// </summary>
    public class HRWorkGrp
    {
        /// <summary>
        /// 获得数据列表
        ///<creator>zhongyl</creator>
        /// <date>2013-07-12</date>
        /// </summary>
        /// <param name="strWhere">SQL判断条件</param>
        /// <returns>数据列表</returns>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select FEntityNo,FWorkGrpNo,FWorkGrpName,FIsUse ");
            strSql.Append(" FROM t_HRWorkGrp ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }
        /// <summary>
        /// 根据公司编号获取数据列表
        /// <creator>wangl</creator>
        /// <date>2013-07-29</date>
        /// </summary>
        /// <param name="entityNo">公司编号</param>
        /// <returns>DataSet对象</returns>
        public DataSet GetListByEntityNo(string entityNo)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT FEntityNo,FWorkGrpNo,FWorkGrpName,FIsUse ");
            strSql.Append(" FROM t_HRWorkGrp ");
            strSql.Append(" WHERE FEntityNo=@FEntityNo AND FIsUse='Y'");
            SqlParameter[] parameters = {
					new SqlParameter("@FEntityNo", SqlDbType.NVarChar,12)};
            parameters[0].Value = entityNo;
            return DbHelperSQL.Query(strSql.ToString(), parameters);
        }
        /// <summary>
        /// 删除工作组（事务处理）
        ///<creator>zhongyl</creator>
        /// <date>2013-07-12</date>
        /// </summary>
        /// <param name="fEntityNo">组织机构编号</param>
        /// <param name="fWorkGrpNo">工作组编号</param>
        public void DeleteWorkGroupAndDuty(string fEntityNo, string fWorkGrpNo)
        {
            List<CommandInfo> cmdList = new List<CommandInfo>();

            //删除工作组基本信息
            CommandInfo cmds = new CommandInfo();
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from t_HRWorkGrp ");
            strSql.Append(" where FEntityNo=@FEntityNo and FWorkGrpNo=@FWorkGrpNo ");
            SqlParameter[] parameters = {
					new SqlParameter("@FEntityNo", SqlDbType.NVarChar,50),
					new SqlParameter("@FWorkGrpNo", SqlDbType.NVarChar,50)};
            parameters[0].Value = fEntityNo;
            parameters[1].Value = fWorkGrpNo;
            cmds.CommandText = strSql.ToString();
            cmds.Parameters = parameters;
            cmdList.Add(cmds);

            //执行存储过程，生成临时表数据，以便查询时调用 
            strSql = new StringBuilder();
            cmds = new CommandInfo();
            strSql.Append("EXEC sp_genDocAuthorityDW @EntityNo");
            SqlParameter[] parameters2 = {
					new SqlParameter("@EntityNo", SqlDbType.NVarChar,12)};
            parameters2[0].Value = fEntityNo;

            cmds.CommandText = strSql.ToString();
            cmds.Parameters = parameters2;
            cmdList.Add(cmds);

            DbHelperSQL.ExecuteSqlTranWithString(cmdList, 1800);
        }


        /// <summary>
        /// 增加工作组（事务处理）
        ///<creator>zhongyl</creator>
        /// <date>2013-07-12</date>
        /// </summary>
        /// <param name="model">工作组信息</param>
        /// <param name="dutys">增加的职位列表</param>
        public void AddWorkGroupAndDuty(Model.HRWorkGrp model, List<Model.HRWorkGrpDuty> dutys)
        {
            List<CommandInfo> cmdList = new List<CommandInfo>();

            //增加工作组基本信息
            CommandInfo cmds = new CommandInfo();
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into t_HRWorkGrp(");
            strSql.Append("FEntityNo,FWorkGrpNo,FWorkGrpName,FIsUse)");
            strSql.Append(" values (");
            strSql.Append("@FEntityNo,@FWorkGrpNo,@FWorkGrpName,@FIsUse)");
            SqlParameter[] parameters = {
					new SqlParameter("@FEntityNo", SqlDbType.NVarChar,12),
					new SqlParameter("@FWorkGrpNo", SqlDbType.NVarChar,12),
					new SqlParameter("@FWorkGrpName", SqlDbType.NVarChar,30),
					new SqlParameter("@FIsUse", SqlDbType.Char,1)};
            parameters[0].Value = model.FEntityNo;
            parameters[1].Value = model.FWorkGrpNo;
            parameters[2].Value = model.FWorkGrpName;
            parameters[3].Value = model.FIsUse;
            cmds.CommandText = strSql.ToString();
            cmds.Parameters = parameters;
            cmdList.Add(cmds);

            //增加工作组职务
            if (dutys != null && dutys.Count > 0)
            {
                foreach (Model.HRWorkGrpDuty grpDuty in dutys)
                {
                    cmds = new CommandInfo();
                    strSql = new StringBuilder();
                    strSql.Append("insert into t_HRWorkGrpDuty(");
                    strSql.Append("FEntityNo,FWorkGrpNo,FDutyProp,FWorkGrpDutyNo,FSeq,FDutyName)");
                    strSql.Append(" values (");
                    strSql.Append("@FEntityNo,@FWorkGrpNo,@FDutyProp,@FWorkGrpDutyNo,@FSeq,@FDutyName)");
                    SqlParameter[] parameter = {
					new SqlParameter("@FEntityNo", SqlDbType.NVarChar,12),
					new SqlParameter("@FWorkGrpNo", SqlDbType.NVarChar,12),
					new SqlParameter("@FDutyProp", SqlDbType.Char,1),
					new SqlParameter("@FWorkGrpDutyNo", SqlDbType.NVarChar,12),
					new SqlParameter("@FSeq", SqlDbType.Int,4),
					new SqlParameter("@FDutyName", SqlDbType.NVarChar,16)};
                    parameter[0].Value = grpDuty.FEntityNo;
                    parameter[1].Value = grpDuty.FWorkGrpNo;
                    parameter[2].Value = grpDuty.FDutyProp;
                    parameter[3].Value = grpDuty.FWorkGrpDutyNo;
                    parameter[4].Value = grpDuty.FSeq;
                    parameter[5].Value = grpDuty.FDutyName;
                    cmds.CommandText = strSql.ToString();
                    cmds.Parameters = parameter;
                    cmdList.Add(cmds);
                }
            }

            //执行存储过程，生成临时表数据，以便查询时调用 
            strSql = new StringBuilder();
            cmds = new CommandInfo();
            strSql.Append("EXEC sp_genDocAuthorityDW @EntityNo");
            SqlParameter[] parameters2 = {
					new SqlParameter("@EntityNo", SqlDbType.NVarChar,12)};
            parameters2[0].Value = model.FEntityNo;

            cmds.CommandText = strSql.ToString();
            cmds.Parameters = parameters2;
            cmdList.Add(cmds);

            DbHelperSQL.ExecuteSqlTranWithString(cmdList, 1800);
        }

        /// <summary>
        /// 修改工作组（事务处理）
        ///<creator>zhongyl</creator>
        /// <date>2013-07-12</date>
        /// </summary>
        /// <param name="model">工作组信息</param>
        /// <param name="dutys">增加的职位列表</param>
        public void UpdateWorkGroupAndDuty(Model.HRWorkGrp model, List<Model.HRWorkGrpDuty> dutys)
        {
            List<CommandInfo> cmdList = new List<CommandInfo>();

            //修改工作组基本信息
            CommandInfo cmds = new CommandInfo();
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update t_HRWorkGrp set ");
            strSql.Append("FWorkGrpName=@FWorkGrpName,");
            strSql.Append("FIsUse=@FIsUse");
            strSql.Append(" where FEntityNo=@FEntityNo and FWorkGrpNo=@FWorkGrpNo ");
            SqlParameter[] parameters = {
					new SqlParameter("@FEntityNo", SqlDbType.NVarChar,12),
					new SqlParameter("@FWorkGrpNo", SqlDbType.NVarChar,12),
					new SqlParameter("@FWorkGrpName", SqlDbType.NVarChar,30),
					new SqlParameter("@FIsUse", SqlDbType.Char,1)};
            parameters[0].Value = model.FEntityNo;
            parameters[1].Value = model.FWorkGrpNo;
            parameters[2].Value = model.FWorkGrpName;
            parameters[3].Value = model.FIsUse;
            cmds.CommandText = strSql.ToString();
            cmds.Parameters = parameters;
            cmdList.Add(cmds);

            //增加工作组职务
            if (dutys != null && dutys.Count > 0)
            {
                foreach (Model.HRWorkGrpDuty grpDuty in dutys)
                {
                    cmds = new CommandInfo();
                    strSql = new StringBuilder();
                    strSql.Append("insert into t_HRWorkGrpDuty(");
                    strSql.Append("FEntityNo,FWorkGrpNo,FDutyProp,FWorkGrpDutyNo,FSeq,FDutyName)");
                    strSql.Append(" values (");
                    strSql.Append("@FEntityNo,@FWorkGrpNo,@FDutyProp,@FWorkGrpDutyNo,@FSeq,@FDutyName)");
                    SqlParameter[] parameter = {
					new SqlParameter("@FEntityNo", SqlDbType.NVarChar,12),
					new SqlParameter("@FWorkGrpNo", SqlDbType.NVarChar,12),
					new SqlParameter("@FDutyProp", SqlDbType.Char,1),
					new SqlParameter("@FWorkGrpDutyNo", SqlDbType.NVarChar,12),
					new SqlParameter("@FSeq", SqlDbType.Int,4),
					new SqlParameter("@FDutyName", SqlDbType.NVarChar,16)};
                    parameter[0].Value = grpDuty.FEntityNo;
                    parameter[1].Value = grpDuty.FWorkGrpNo;
                    parameter[2].Value = grpDuty.FDutyProp;
                    parameter[3].Value = grpDuty.FWorkGrpDutyNo;
                    parameter[4].Value = grpDuty.FSeq;
                    parameter[5].Value = grpDuty.FDutyName;
                    cmds.CommandText = strSql.ToString();
                    cmds.Parameters = parameter;
                    cmdList.Add(cmds);
                }
            }

            //执行存储过程，生成临时表数据，以便查询时调用 
            strSql = new StringBuilder();
            cmds = new CommandInfo();
            strSql.Append("EXEC sp_genDocAuthorityDW @EntityNo");
            SqlParameter[] parameters2 = {
					new SqlParameter("@EntityNo", SqlDbType.NVarChar,12)};
            parameters2[0].Value = model.FEntityNo;

            cmds.CommandText = strSql.ToString();
            cmds.Parameters = parameters2;
            cmdList.Add(cmds);

            DbHelperSQL.ExecuteSqlTranWithString(cmdList, 1800);
        }

        /// <summary>
        /// 得到一个实体对象
        ///<creator>zhongyl</creator>
        /// <date>2013-07-12</date>
        /// </summary>
        /// <param name="fEntityNo">组织机构编号</param>
        /// <param name="fWorkGrpNo">工作组编号</param>
        /// <returns>工作组信息</returns>
        public Model.HRWorkGrp GetModel(string fEntityNo, string fWorkGrpNo)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 FEntityNo,FWorkGrpNo,FWorkGrpName,FIsUse from t_HRWorkGrp ");
            strSql.Append(" where FEntityNo=@FEntityNo and FWorkGrpNo=@FWorkGrpNo ");
            SqlParameter[] parameters = {
					new SqlParameter("@FEntityNo", SqlDbType.NVarChar,50),
					new SqlParameter("@FWorkGrpNo", SqlDbType.NVarChar,50)};
            parameters[0].Value = fEntityNo;
            parameters[1].Value = fWorkGrpNo;

            Model.HRWorkGrp model = new Model.HRWorkGrp();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                model.FEntityNo = ds.Tables[0].Rows[0]["FEntityNo"].ToString();
                model.FWorkGrpNo = ds.Tables[0].Rows[0]["FWorkGrpNo"].ToString();
                model.FWorkGrpName = ds.Tables[0].Rows[0]["FWorkGrpName"].ToString();
                model.FIsUse = ds.Tables[0].Rows[0]["FIsUse"].ToString();
                return model;
            }
            else
            {
                return null;
            }
        }


    }
}
